import sys
sys.path.append('.')
from typing import List, Union
from src.interface import IDataAdapter
from src.models import User, Task
import sqlite3 as sql
import os, uuid, re

class SqliteDataError(Exception):
    '''读取数据库文件时数据错误'''
    def __init__(self, *args: object) -> None:
        super().__init__(*args)

class SqliteItemExistError(Exception):
    '''数据库中对象已存在'''
    def __init__(self, *args: object) -> None:
        super().__init__(*args)

class SqliteItemNotFoundError(Exception):
    '''数据库中对象不存在'''
    def __init__(self, *args: object) -> None:
        super().__init__(*args)

class SqliteDatabaseNotAssignError(Exception):
    '''数据库未指定'''
    def __init__(self, *args: object) -> None:
        super().__init__(*args)


class SqliteAdapter(IDataAdapter):

    def __init__(self, users_path : str = './data/users.db'):
        
        with sql.connect(database = users_path) as self.usr_con:
            self.usr_cur = sql.Cursor(self.usr_con)
        self.current_user = ''
        self.tsk_con : Union[sql.Connection,None] = None
        self.tsk_cur : Union[sql.Cursor,None] = None

        res = self.usr_cur.execute('SELECT name FROM sqlite_master')

        if ('users',) not in res.fetchall():
            self.usr_cur.execute('''CREATE TABLE users(
                            uuid TEXT PRIMARY KEY NOT NULL,
                            name TEXT NOT NULL UNIQUE,
                            password TEXT NOT NULL
                            );''')
            self.usr_con.commit()



    def _save_tasks(self, tasks : List[Task]) -> bool:
        
        if self.current_user and isinstance(self.tsk_cur, sql.Cursor) and isinstance(self.tsk_con, sql.Connection):
            self.tsk_cur.execute('DELETE FROM tasks')
            data = []
            for i in tasks:
                data.append(tuple(i))
            self.tsk_cur.executemany(f'INSERT INTO {self.current_user} VALUES(?,?,?)', data)
            self.tsk_con.commit()
            return True
            
        else:
            raise SqliteDatabaseNotAssignError('Haven\'t assign user')

    def _load_tasks(self) -> List[Task]:
        
        if self.current_user and isinstance(self.tsk_cur, sql.Cursor) and isinstance(self.tsk_con, sql.Connection):
            res = self.tsk_cur.execute('''SELECT * FROM tasks''')
            res_list = res.fetchall()
            result = []
            for i in res_list:
                task = Task(uuid = i[0],
                            title = i[1],
                            deadline = i[2],
                            content = i[3],
                            status = i[4],
                            create_time = i[5],
                            complete_time = i[6])
                result.append(task)
            return result
            
        else:
            raise SqliteDatabaseNotAssignError('Haven\'t assign user')

    def _save_users(self, users : List[User]) -> bool:
        self.usr_cur.execute('DELETE FROM users')
        data = []
        for i in users:
            data.append(tuple(i))
        self.usr_cur.executemany('INSERT INTO users VALUES(?,?,?)', data)
        self.usr_con.commit()
        return True

    def _load_users(self) -> List[User]:

        res = self.usr_cur.execute('''SELECT * FROM users''')
        users = res.fetchall()
        result = []

        for i in users:
            try:
                result.append(User(uuid=i[0], name=i[1], password=i[2]))
            except:
                raise SqliteDataError('Data file are corrupt!')
        return result
                


    def add_task(self, task: Task) -> bool:
        
        if self.current_user and isinstance(self.tsk_cur, sql.Cursor) and isinstance(self.tsk_con, sql.Connection):
            try:
                self.tsk_cur.execute('INSERT INTO users VALUES(?,?,?,?,?,?,?)', tuple(task))
                self.tsk_con.commit()
                return True
            
            except sql.IntegrityError:
                raise SqliteItemExistError('Task exist!')
            
        else:
            raise SqliteDatabaseNotAssignError('Haven\'t assign user')
        
    def select_task(self, uuid : uuid.UUID) -> Task:
        
        if self.current_user and isinstance(self.tsk_cur, sql.Cursor) and isinstance(self.tsk_con, sql.Connection):
            res = self.tsk_cur.execute(f'SELECT * FROM tasks WHERE uuid == {str(uuid)}')
            tsk_tpl = res.fetchall()[0]
            return Task(uuid = tsk_tpl[0],
                        title = tsk_tpl[1],
                        deadline = tsk_tpl[2],
                        content = tsk_tpl[3],
                        status = tsk_tpl[4],
                        create_time = tsk_tpl[5],
                        complete_time = tsk_tpl[6])

        else:
            raise SqliteDatabaseNotAssignError('Haven\'t assign user')

    def search_task(self, **kwarg) -> List[Task]:
        '''根据标题、内容、时间、状态自由·组合筛选条件
        arg:
            title : str(任务标题)
            content : str(任务内容)
            deadline_interval : Tuple[datetime, datetime](任务截止时间)
            create_time_interval : Tuple[datetime, datetime](任务创建时间)
            complete_time_interval : Tuple[datetime, datetime](任务完成时间)
            status : int(任务状态)
        '''
        if self.current_user and isinstance(self.tsk_cur, sql.Cursor) and isinstance(self.tsk_con, sql.Connection):
            result : List[Task] = []
            res_list = self.tsk_cur.execute('SELECT * FROM tasks').fetchall()
            for i in res_list:
                result.append(Task(uuid = i[0],
                                    title = i[1],
                                    deadline = i[2],
                                    content = i[3],
                                    status = i[4],
                                    create_time = i[5],
                                    complete_time = i[6]))
                
            if title := kwarg.get('title', False):
                new_result = []
                for i in result:
                    if re.match(rf'{title}', i.title):
                        new_result.append(i)
                result = new_result

            if content := kwarg.get('content', False):
                new_result = []
                for i in result:
                    if re.match(rf'{content}', i.content):
                        new_result.append(i)
                result = new_result

            if deadline_interval := kwarg.get('deadline_interval', False):
                new_result = []
                for i in result:
                    if deadline_interval[0] <= i.deadline <= deadline_interval[1]:
                        new_result.append(i)
                result = new_result

            if create_time_interval := kwarg.get('create_time_interval', False):
                new_result = []
                for i in result:
                    if create_time_interval[0] <= i.create_time <= create_time_interval[1]:
                        new_result.append(i)
                result = new_result

            if complete_time_interval := kwarg.get('complete_time_interval', False):
                new_result = []
                for i in result:
                    if complete_time_interval[0] <= i.complete_time <= complete_time_interval[1]:
                        new_result.append(i)
                result = new_result

            if status := kwarg.get('status', False):
                new_result = []
                for i in result:
                    if i.status == status:
                        new_result.append(i)
                result = new_result
            return result

        else:
            raise SqliteDatabaseNotAssignError('Haven\'t assign user')
        
    def delete_task(self, uuid : uuid.UUID) -> bool:
        '''删除任务'''
        if self.current_user and isinstance(self.tsk_cur, sql.Cursor) and isinstance(self.tsk_con, sql.Connection):
            try:
                self.tsk_cur.execute(f'DELETE FROM tasks WHERE uuid == {str(uuid)}')
                self.tsk_con.commit()
                return True
            except:
                raise SqliteItemNotFoundError('Task not found!')
        else:
            raise SqliteDatabaseNotAssignError('Haven\'t assign user')

    def update_task(self, uuid : uuid.UUID, **kwarg) -> bool:
        '''更新任务信息'''
        if self.current_user and isinstance(self.tsk_cur, sql.Cursor) and isinstance(self.tsk_con, sql.Connection):
            try:
                if title := kwarg.get('title', False):
                    self.tsk_cur.execute(f'UPDATE tasks SET title = {title} WHERE uuid = {str(uuid)}')

                if deadline := kwarg.get('deadline', False):
                    self.tsk_cur.execute(f'UPDATE tasks SET deadline = {deadline} WHERE uuid = {str(uuid)}')

                if content := kwarg.get('content', False):
                    self.tsk_cur.execute(f'UPDATE tasks SET content = {content} WHERE uuid = {str(uuid)}')

                if status := kwarg.get('status', False):
                    self.tsk_cur.execute(f'UPDATE tasks SET status = {status} WHERE uuid = {str(uuid)}')

                if create_time := kwarg.get('create_time', False):
                    self.tsk_cur.execute(f'UPDATE tasks SET create_time = {create_time} WHERE uuid = {str(uuid)}')

                if complete_time := kwarg.get('complete_time', False):
                    self.tsk_cur.execute(f'UPDATE tasks SET complete_time = {complete_time} WHERE uuid = {str(uuid)}')

                self.tsk_con.commit()
                return True
            
            except:
                raise SqliteItemNotFoundError('Task not found!')
        else:
            raise SqliteDatabaseNotAssignError('Haven\'t assign user')



    def add_user(self, user : User) -> bool:

        try:
            self.usr_cur.execute('INSERT INTO users VALUES(?,?,?)', tuple(user))
            self.usr_con.commit
            return True
        
        except sql.IntegrityError:
            raise SqliteItemExistError('User exist!')
        
    def select_user(self, uuid : uuid.UUID) -> User:
        
        res = self.usr_cur.execute(f'SELECT * FROM users WHERE uuid == {str(uuid)}')
        if res_list := res.fetchall():
            user_tpl = res_list[0]
            result = User(uuid=user_tpl[0],name=user_tpl[1],password=user_tpl[2],is_hash=True)
            return result
        else:
            raise SqliteItemNotFoundError(f"Cannot find user {uuid}")
        
    def search_user(self, **kwarg) -> User:
        
        if 'name' in kwarg.keys():
            res = self.usr_cur.execute(f'''SELECT * FROM users WHERE name == {kwarg.get('name')}''')
            res_list = res.fetchall()
            if res_list:
                user_tpl = res_list[0]
                result = User(uuid=user_tpl[0],name=user_tpl[1],password=user_tpl[2],is_hash=True)
                return result
            else:
                raise SqliteItemNotFoundError('User not found!')
            
        else:
            raise ValueError('argument "name" is required')
        
    def delete_user(self, uuid : uuid.UUID) -> bool:
        '''删除用户'''
        try:
            self.usr_cur.execute(f'DELETE FROM users WHERE uuid == {str(uuid)}')
            self.usr_con.commit()
            return True
        except:
            raise SqliteItemNotFoundError('User not found!')
        
    def update_user(self, uuid : uuid.UUID, **kwarg) -> bool:
        '''更新用户信息'''
        try:
            if name := kwarg.get('name', False):
                self.usr_cur.execute(f'UPDATE users SET name = {name} WHERE uuid = {str(uuid)}')

            if password := kwarg.get('password', False):
                self.usr_cur.execute(f'UPDATE users SET password = {User.str_to_hash(password)} WHERE uuid = {str(uuid)}')

            self.usr_con.commit()
            return True

        except:
            raise SqliteItemNotFoundError('User not found!')

    def switch_user(self, user : User) -> bool:

        if not os.path.exists(f'./data/tasks/{user.name}.db'):
            with sql.connect(f'./data/tasks/{user.name}.db') as self.tsk_con:
                self.tsk_cur = sql.Cursor(self.tsk_con)
            self.tsk_cur.execute(f'''CREATE TABLE tasks(
                            uuid TEXT PRIMARY KEY NOT NULL,
                            tiltle TEXT NOT NULL,
                            deadline TEXT NOT NULL,
                            content TEXT,
                            status INTEGER NOT NULL,
                            create_time TEXT NOT NULL,
                            complete_time TEXT
                            );''')
            self.tsk_con.commit()
            return True
        
        else:
            with sql.connect(f'./data/tasks/{user.name}.db') as self.tsk_con:
                self.tsk_cur = sql.Cursor(self.tsk_con)
            return True
        
        


